CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_WEEKEND" is



------------------------------------------- calculate the basic data such as open price, close price and so on -------------------------
PROCEDURE WRITE_STOCK_WEEK as
    -- 表示某只股票的STOCK_CODE字段
    v_code varchar2(10);
    -- 某只股票的第一条记录的日期之后的下一个星期一的日期
    v_first_next_monday date;
    -- 定义一个含有5个stock_moving_average记录的数组
    type type_array is varray(6360) of stock_transaction_data%rowtype;
    array_stock_week_record type_array:=type_array();
    -- 表示一个星期中的开盘价，收盘价，最高价和最低价
    v_stock_week_open_price number;
    v_stock_week_close_price number;
    v_stock_week_highest_price number;
    v_stock_week_lowest_price number;
    -- 表示这个星期的交易日数
    v_day_num_in_week number;
    -- 用来重置序列seq_id
    v_seq_num number;
    -- 表示这个星期这只股票的总成交量
    v_week_volume number;
    -- 返回STOCK_TRANSACTION_DATA表中的code_字段（不包括重复的）
    cursor cur_all_stock_code is select distinct std.code_ from stock_transaction_data std order by std.code_ asc;
    -- 根据v_code变量，在表STOCK_TRANSACTION_DATA中查找某只股票的全部记录，并按升序排列
    cursor cur_single_stock_data is select * from stock_transaction_data std where std.code_=v_code order by std.date_ asc;
begin
    -- 需要先给v_week_volume赋初值，否则第一支股票的第一个交易周将没有成交量
    v_week_volume:=0;
    for i in cur_all_stock_code loop
        v_code:=i.code_;
        -- 重置序列seq_id
        select -seq_id.nextval into v_seq_num from dual;
        execute immediate 'alter sequence seq_id increment by '||v_seq_num;
        select seq_id.nextval into v_seq_num from dual;
        execute immediate 'alter sequence seq_id increment by 1';
        -- 初始化变量v_first_next_monday。next_day函数的第二个参数为1时表示星期日，为2时才表示星期一。
        select next_day(std.date_, 2) into v_first_next_monday from stock_transaction_data std where std.code_=v_code and rownum<=1 order by std.date_ asc;
        -- 注意：如果不添加这一行，每只股票都会出现开始日期大于结束日期的记录（第一只股票除外）
        array_stock_week_record:=type_array();
        for j in cur_single_stock_data loop
            if j.date_<v_first_next_monday then -- 如果是属于同一个星期的记录就保存到数组中
                array_stock_week_record.extend;
                array_stock_week_record(array_stock_week_record.count):=j;
            else
                -- 计算一个星期中的开盘价，收盘价，最高价和最低价
                v_stock_week_open_price:=array_stock_week_record(1).open_price;
                v_stock_week_close_price:=array_stock_week_record(array_stock_week_record.count).close_price;
                if v_stock_week_open_price>v_stock_week_close_price then
                   v_stock_week_highest_price:=v_stock_week_open_price;
                   v_stock_week_lowest_price:=v_stock_week_close_price;
                end if;
                if v_stock_week_open_price<v_stock_week_close_price then
                   v_stock_week_highest_price:=v_stock_week_close_price;
                   v_stock_week_lowest_price:=v_stock_week_open_price;
                end if;
                for x in 1..array_stock_week_record.count loop
                    if array_stock_week_record(x).highest_price>v_stock_week_highest_price then
                        v_stock_week_highest_price:=array_stock_week_record(x).highest_price;
                    end if;
                end loop;
                for x in 1..array_stock_week_record.count loop
                    if array_stock_week_record(x).lowest_price<v_stock_week_lowest_price then
                        v_stock_week_lowest_price:=array_stock_week_record(x).lowest_price;
                    end if;
                end loop;
                -- 这个星期这只股票的总成交量
                for x in 1..array_stock_week_record.count loop
                    v_week_volume:=v_week_volume+array_stock_week_record(x).volume;
                end loop;
                -- 插入这个星期的数据
                v_day_num_in_week:=array_stock_week_record.count;
                insert into stock_week(ID_, BEGIN_DATE, END_DATE, NUMBER_, OPEN_PRICE, CLOSE_PRICE, HIGHEST_PRICE,
                       LOWEST_PRICE, VOLUME)
                values(v_code, array_stock_week_record(1).date_, array_stock_week_record(v_day_num_in_week).date_,
                               SEQ_ID.NEXTVAL, v_stock_week_open_price, v_stock_week_close_price,
                               v_stock_week_highest_price, v_stock_week_lowest_price, v_week_volume);
                -- 为下一轮迭代做准备
                select next_day(j.date_, 2) into v_first_next_monday from dual;
                array_stock_week_record:=type_array();
                array_stock_week_record.extend;
                array_stock_week_record(array_stock_week_record.count):=j;
                v_week_volume:=0;
            end if;

        end loop;
    end loop;
end WRITE_STOCK_week;



-------------------------------------------------------------- calculate KD --------------------------------------------------
procedure WRITE_WEEKEND_KD_INIT as
    -- 表示某只股票的STOCK_CODE字段
    stockCode varchar2(10);
    -- 用于计算是否是第9个交易周
    num number;
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 查询某只股票最初的8个交易周的记录，并按生序排列
    cursor singleStockK is select * from stock_weekend t where t.stock_code=stockCode and rownum<=8 order by t.weekend_end_date asc;
begin
    -- 初始化每只股票第一个交易周的K和D字段
    for i in allStockCode loop
        stockCode:=i.stock_code;
        num:=0;
        for j in singleStockK loop
            num:=num+1;
            if num=8 then
                -- 若无前一日K 值与D值，则可分别用50来代替
                update stock_weekend t set t.k=50,t.d=50 where t.stock_code=stockCode and t.weekend_end_date=j.weekend_end_date;
                commit;
            end if;
        end loop;
    end loop;
end WRITE_WEEKEND_KD_INIT;

procedure WRITE_WEEKEND_KD_RSV as
    -- 表示某只股票的STOCK_CODE字段
    stockCode varchar2(10);
    -- 9日内最高价
    nineDayHighestPrice number;
    -- 9日内最低价
    nineDayLowestPrice number;
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 获取某只股票所有的周线级别的交易记录，并按升序排列
    cursor singleStock is select * from stock_weekend t where t.stock_code=stockCode order by t.weekend_end_date asc;
begin
    for i in allStockCode loop
        stockCode:=i.stock_code;
        num:=0;
        for j in singleStock loop
            num:=num+1;
            if num>=9 then
                -- 计算9日内最高价和最低价
                select max(weekend_high),min(weekend_low) into nineDayHighestPrice,nineDayLowestPrice
                from (select * from stock_weekend t where t.stock_code=stockCode and t.weekend_end_date<=j.weekend_end_date
                     order by t.weekend_end_date desc)
                where rownum<=9;
                -- 计算rsv
                update stock_weekend t set t.rsv=(t.weekend_close-nineDayLowestPrice)/(nineDayHighestPrice-nineDayLowestPrice)*100
                where t.stock_code=stockCode and t.weekend_end_date=j.weekend_end_date;
                commit;
            end if;
        end loop;
    end loop;
end WRITE_WEEKEND_KD_RSV;

procedure WRITE_WEEKEND_KD_K as
    -- 表示某只股票的STOCK_CODE字段
    stockCode varchar2(10);
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- 表示KD指标的K
    tempK number;
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 获取某只股票所有的周线级别的交易记录，并按升序排列
    cursor singleStock is select * from stock_weekend t where t.stock_code=stockCode order by t.weekend_end_date asc;
begin
    for i in allStockCode loop
        stockCode:=i.stock_code;
        num:=0;
        for j in singleStock loop
            num:=num+1;
            if num=8 then
                tempK:=j.k;
            end if;
            if num>=9 then
                -- 计算K
                update stock_weekend t set t.k=2/3*tempK+1/3*t.rsv where t.stock_code=stockCode and t.weekend_end_date=j.weekend_end_date;
                commit;
                select t.k into tempK from stock_weekend t where t.stock_code=stockCode and t.weekend_end_date=j.weekend_end_date;
            end if;

        end loop;
    end loop;
end WRITE_WEEKEND_KD_K;

procedure WRITE_WEEKEND_KD_D as
    -- 表示某只股票的STOCK_CODE字段
    stockCode varchar2(10);
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- 表示KD指标的D
    tempD number;
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 获取某只股票所有的周线级别的交易记录，并按升序排列
    cursor singleStock is select * from stock_weekend t where t.stock_code=stockCode order by t.weekend_end_date asc;
begin
    for i in allStockCode loop
        stockCode:=i.stock_code;
        num:=0;
        for j in singleStock loop
            num:=num+1;
            if num=8 then
                tempD:=j.d;
            end if;
            if num>=9 then
                -- 计算D
                update stock_weekend t set t.d=2/3*tempD+1/3*j.k where t.stock_code=stockCode and t.weekend_end_date=j.weekend_end_date;
                commit;
                select t.d into tempD from stock_weekend t where t.stock_code=stockCode and t.weekend_end_date=j.weekend_end_date;
            end if;
        end loop;
    end loop;
end WRITE_WEEKEND_KD_D;


--------------------- calculate weekend_up_down -----------------------------
procedure WRITE_WEEKEND_UP_DOWN as
    -- 表示某只股票的STOCK_CODE字段
    stockCode varchar2(10);
     -- 表示前一周的weekend_close
    preWeekendClose number;
    -- 表示某只股票第一周的weekend_close
    initWeekendClose number;
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 返回某只股票所有交易周的收盘价
    cursor singleWeekendClose is select * from stock_weekend t where t.stock_code=stockCode order by t.weekend_end_date asc;
begin
    for i in allStockCode loop
        stockCode:=i.stock_code;
        select t.weekend_close into initWeekendClose from stock_weekend t where t.stock_code=stockCode and rownum<=1 order by t.weekend_end_date asc;
              preWeekendClose:=initWeekendClose;
              for j in singleWeekendClose loop
                  if preWeekendClose=j.weekend_close then
                     update stock_weekend t set t.weekend_up_down=0 where t.stock_code=j.stock_code and t.weekend_begin_date=j.weekend_begin_date and t.weekend_end_date=j.weekend_end_date;
                  elsif preWeekendClose<j.weekend_close then
                     update stock_weekend t set t.weekend_up_down=1 where t.stock_code=j.stock_code and t.weekend_begin_date=j.weekend_begin_date and t.weekend_end_date=j.weekend_end_date;
                  else
                     update stock_weekend t set t.weekend_up_down=-1 where t.stock_code=j.stock_code and t.weekend_begin_date=j.weekend_begin_date and t.weekend_end_date=j.weekend_end_date;
                  end if;
                  preWeekendClose:=j.weekend_close;
              end loop;
    end loop;
end WRITE_WEEKEND_UP_DOWN;


/*------------------------------- judge whether the close of stock is up or down according the given weekend -------------------------------*/
procedure WRITE_WEEKEND_UP_DOWN_BY_DATE(weekendBeginDate in varchar2,weekendEndDate in varchar2) as
    -- 表示stock_code
    stockCode varchar2(10);
    -- 表示当前股票某一周的收盘价
    currentWeekendClose number;
    -- 表示当前股票某一日的日期
    currentWeekendEndDate Date;
    -- define cursor section.返回全部stock_code。
    cursor allStockCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 返回某只股票从某一日开始最近两天的交易记录
    cursor twoStock is select * from stock_weekend t where t.stock_code=stockCode and t.weekend_begin_date<=to_date(weekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date<=to_date(weekendEndDate,'yyyy-mm-dd') and rownum<=2 order by t.weekend_end_date desc;
begin
        for i in allStockCode loop
            stockCode:=i.stock_code;
            -- 返回某只股票某一日的收盘价和日期
            select t.weekend_close,t.weekend_end_date into currentWeekendClose,currentWeekendEndDate from stock_weekend t where t.stock_code=stockCode and t.weekend_begin_date<=to_date(weekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date<=to_date(weekendEndDate,'yyyy-mm-dd') and rownum<=1 order by t.weekend_end_date desc;
            for j in twoStock loop
                if currentWeekendEndDate!=j.weekend_end_date then
                   if j.weekend_close>currentWeekendClose then
                      update stock_weekend t set t.weekend_up_down=-1 where t.stock_code=i.stock_code and t.weekend_begin_date=to_date(weekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date=to_date(weekendEndDate,'yyyy-mm-dd');
                   elsif j.weekend_close<currentWeekendClose then
                      update stock_weekend t set t.weekend_up_down=1 where t.stock_code=i.stock_code and t.weekend_begin_date=to_date(weekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date=to_date(weekendEndDate,'yyyy-mm-dd');
                   else
                      update stock_weekend t set t.weekend_up_down=0 where t.stock_code=i.stock_code and t.weekend_begin_date=to_date(weekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date=to_date(weekendEndDate,'yyyy-mm-dd');
                   end if;
               end if;
           end loop;
       end loop;
end WRITE_WEEKEND_UP_DOWN_BY_DATE;


--------------------- calculate the basic data such as open price, close price and so on by date -----------------------------
procedure WRITE_STOCK_WEEKEND_BY_DATE(beginDate varchar2,endDate varchar2) as
    -- 表示某只股票的STOCK_CODE字段
    stockCode varchar2(10);
    -- 分别代表某只股票在某个星期的最高价，最低价，开盘价，收盘价，成交量和序列号
    highPrice number;
    lowPrice number;
    openPrice number;
    closePrice number;
    sumAmount number;
    stockSeqId number;
    num number;
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
begin
    for i in allStockCode loop
        stockCode:=i.stock_code;
        select count(*) into num from stock_moving_average t where t.stock_code=stockCode and t.stock_date between to_date(beginDate,'yyyy-mm-dd') and to_date(endDate,'yyyy-mm-dd');
        if num!=0 then
            dbms_output.put_line(stockCode);
            -- 计算某只股票在某个星期的最高价，最低价，开盘价，收盘价和成交量
            select max(t.stock_high),min(t.stock_low) into highPrice,lowPrice from stock_moving_average t where t.stock_code=stockCode and t.stock_date between to_date(beginDate,'yyyy-mm-dd') and to_date(endDate,'yyyy-mm-dd') order by t.stock_date asc;
            select t.stock_open into openPrice from stock_moving_average t where t.stock_code=stockCode and t.stock_date between to_date(beginDate,'yyyy-mm-dd') and to_date(endDate,'yyyy-mm-dd') and rownum<=1 order by t.stock_date asc;
            select t.stock_close into closePrice from stock_moving_average t where t.stock_code=stockCode and t.stock_date between to_date(beginDate,'yyyy-mm-dd') and to_date(endDate,'yyyy-mm-dd') and rownum<=1 order by t.stock_date desc;
            select sum(t.stock_amount) into sumAmount from stock_moving_average t where t.stock_code=stockCode and t.stock_date between to_date(beginDate,'yyyy-mm-dd') and to_date(endDate,'yyyy-mm-dd');
            select max(t.weekend_number) into stockSeqId from stock_weekend t where t.stock_code=stockCode order by t.weekend_end_date desc;

            -- 如果是一只新股，则在表stock_moving_average有记录，但是在表stock_weekend中就没有记录。因此应当初始化stockSeqId为0，否则stockSeqId为null，会报错
            if stockSeqId is null then
                stockSeqId:=0;
            end if;

            insert into stock_weekend(stock_code,weekend_begin_date,weekend_end_date,weekend_number,weekend_high,weekend_low,weekend_open,weekend_close,weekend_amount)
            values(stockCode,to_date(beginDate,'yyyy-mm-dd'),to_date(endDate,'yyyy-mm-dd'),stockSeqId+1,highPrice,lowPrice,openPrice,closePrice,sumAmount);
            commit;
        end if;
    end loop;
end WRITE_STOCK_WEEKEND_BY_DATE;



---------------------------------------------------------- calculate KD by date -----------------------------------------------
procedure WRITE_WEEKEND_KD_BY_DATE_RSV(beginDate varchar2,endDate varchar2) as
    -- 表示某只股票的STOCK_CODE字段
    stockCode varchar2(10);
    -- 9日内最高价
    nineDayHighestPrice number;
    -- 9日内最低价
    nineDayLowestPrice number;
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
begin
    for i in allStockCode loop
        stockCode:=i.stock_code;
        -- 计算9日内最高价和最低价
        select max(weekend_high),min(weekend_low) into nineDayHighestPrice,nineDayLowestPrice from (
           select * from stock_weekend t where t.stock_code=stockCode and t.weekend_begin_date<=to_date(beginDate,'yyyy-mm-dd')
           and t.weekend_end_date<=to_date(endDate,'yyyy-mm-dd')
           order by t.weekend_end_date desc)
        where rownum<=9;
        -- 计算某只股票某一周的RSV
        update stock_weekend t set t.rsv=(t.weekend_close-nineDayLowestPrice)/(nineDayHighestPrice-nineDayLowestPrice)*100
        where t.stock_code=stockCode and t.weekend_begin_date<=to_date(beginDate,'yyyy-mm-dd') and t.weekend_end_date<=to_date(endDate,'yyyy-mm-dd');
        commit;
    end loop;
end WRITE_WEEKEND_KD_BY_DATE_RSV;

procedure WRITE_WEEKEND_KD_BY_DATE_K(beginDate varchar2,endDate varchar2) as
    -- 表示某只股票的STOCK_CODE字段
    stockCode varchar2(10);
    -- 表示前一日K值
    tempK number;
    -- 用于计数
    num number;
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 按照日期参数，获取某只股票最近两天的周线级别的交易记录，并按降序排列
    cursor singleStock is select * from(
           select * from stock_weekend t where t.stock_code=stockCode and t.weekend_begin_date<=to_date(beginDate,'yyyy-mm-dd')
           and t.weekend_end_date<=to_date(endDate,'yyyy-mm-dd') order by t.weekend_begin_date desc)
           where rownum<=2 order by weekend_begin_date asc;
begin
    for i in allStockCode loop
        stockCode:=i.stock_code;
        num:=0;
        for j in singleStock loop
            num:=num+1;
            if num=1 then
               tempK:=j.k;
            end if;
            if num=2 then
                update stock_weekend t set t.k=2/3*tempK+1/3*t.rsv where t.stock_code=stockCode
                and t.weekend_begin_date=to_date(beginDate,'yyyy-mm-dd') and t.weekend_end_date=to_date(endDate,'yyyy-mm-dd');
                commit;
            end if;
        end loop;
    end loop;
end WRITE_WEEKEND_KD_BY_DATE_K;

procedure WRITE_WEEKEND_KD_BY_DATE_D(beginDate varchar2,endDate varchar2) as
    -- 表示某只股票的STOCK_CODE字段
    stockCode varchar2(10);
    -- 表示前一日K值
    tempD number;
    -- 用于计数
    num number;
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 按照日期参数，获取某只股票最近两天的周线级别的交易记录，并按降序排列
    cursor singleStock is select * from(
           select * from stock_weekend t where t.stock_code=stockCode and t.weekend_begin_date<=to_date(beginDate,'yyyy-mm-dd')
           and t.weekend_end_date<=to_date(endDate,'yyyy-mm-dd') order by t.weekend_begin_date desc)
           where rownum<=2 order by weekend_begin_date asc;
begin
    for i in allStockCode loop
        stockCode:=i.stock_code;
        num:=0;
        for j in singleStock loop
            num:=num+1;
            if num=1 then
               tempD:=j.d;
            end if;
            if num=2 then
                update stock_weekend t set t.d=2/3*tempD+1/3*t.k where t.stock_code=stockCode
                and t.weekend_begin_date=to_date(beginDate,'yyyy-mm-dd') and t.weekend_end_date=to_date(endDate,'yyyy-mm-dd');
                commit;
            end if;
        end loop;
    end loop;
end WRITE_WEEKEND_KD_BY_DATE_D;



/*--------------------------------------------------- write weekend MACD of all stocks ---------------------------------------------------*/
procedure WRITE_WEEKEND_MACD_INIT as
   -- 表示stock_code
    stockWeekendCode varchar2(10);
    -- 返回全部stock_code
    cursor allStockWeekendCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 获取每只股票第一个交易周的日期
    cursor firstStockWeekendDate is select t.weekend_begin_date from stock_weekend t where t.stock_code=stockWeekendCode and rownum<=1 order by t.weekend_begin_date asc;
begin
     -- 初始化每只股票第一个交易周的ema12,ema26,dif和dea字段
     for i in allStockWeekendCode loop
         stockWeekendCode:=i.stock_code;
         for j in firstStockWeekendDate loop
             update stock_weekend t set t.ema12=t.weekend_close,t.ema26=t.weekend_close,t.dif=0,t.dea=0 where t.stock_code=stockWeekendCode and t.weekend_begin_date=j.weekend_begin_date;
             commit;
         end loop;
     end loop;
end WRITE_WEEKEND_MACD_INIT;

procedure WRITE_WEEKEND_MACD_EMA as
    preEma12 number;
    preEma26 number;
    preDif number;
    preDea number;
    firstDate date;
   -- 表示stock_code
    stockWeekendCode varchar2(10);
    -- 返回全部stock_code
    cursor allStockWeekendCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 获取每只股票第一个交易周的日期
    cursor firstStockWeekendDate is select t.weekend_begin_date from stock_weekend t where t.stock_code=stockWeekendCode and rownum<=1 order by t.weekend_begin_date asc;
    -- 根据stockCode选出某只股票的除第一周外的全部交易记录，按升序排列
    cursor allStockWeekend is select distinct * from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date>firstDate order by t.weekend_begin_date asc;
begin
    -- 计算每只股票其余交易周的ema12,ema26,dif和dea字段
     for i in allStockWeekendCode loop
         stockWeekendCode:=i.stock_code;
         -- 用记录是第一个交易周的字段初始化相关变量
         for x in firstStockWeekendDate loop
             select t.ema12,t.ema26,t.dif,t.dea into preEma12,preEma26,preDif,preDea from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date=x.weekend_begin_date;
         end loop;

         select t.weekend_begin_date into firstDate from stock_weekend t where t.stock_code=stockWeekendCode and rownum<=1 order by t.weekend_begin_date asc;
         for j in allStockWeekend loop
             -- 对于其余交易周，更新ema12,ema26,dif和dea字段
             update stock_weekend t set t.ema12=preEma12*11/13+j.weekend_close*2/13,t.ema26=preEma26*25/27+j.weekend_close*2/27 where t.stock_code=stockWeekendCode and t.weekend_begin_date=j.weekend_begin_date;
             commit;
             -- 用于计算下一个交易周时使用
             select t.ema12 into preEma12 from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date=j.weekend_begin_date;
             select t.ema26 into preEma26 from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date=j.weekend_begin_date;
         end loop;
     end loop;
end WRITE_WEEKEND_MACD_EMA;

procedure WRITE_WEEKEND_MACD_DIF as
    preEma12 number;
    preEma26 number;
    preDif number;
    preDea number;
    firstDate date;
   -- 表示stock_code
    stockWeekendCode varchar2(10);
    -- 返回全部stock_code
    cursor allStockWeekendCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 获取每只股票第一个交易周的日期
    cursor firstStockWeekendDate is select t.weekend_begin_date from stock_weekend t where t.stock_code=stockWeekendCode and rownum<=1 order by t.weekend_begin_date asc;
    -- 根据stockCode选出某只股票的除第一周以外的全部交易记录，按升序排列
    cursor allStockWeekend is select distinct * from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date>firstDate order by t.weekend_begin_date asc;
begin
    -- 计算每只股票其余交易周的ema12,ema26,dif和dea字段
     for i in allStockWeekendCode loop
         stockWeekendCode:=i.stock_code;
         -- 用记录是第一个交易周的字段初始化相关变量
         for x in firstStockWeekendDate loop
             select t.ema12,t.ema26,t.dif,t.dea into preEma12,preEma26,preDif,preDea from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date=x.weekend_begin_date;
         end loop;
         select t.weekend_begin_date into firstDate from stock_weekend t where t.stock_code=stockWeekendCode and rownum<=1 order by t.weekend_begin_date asc;
         for j in allStockWeekend loop
             update stock_weekend t set t.dif=t.ema12-t.ema26 where t.stock_code=stockWeekendCode and t.weekend_begin_date=j.weekend_begin_date;
             commit;
             select t.dif into preDif from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date=j.weekend_begin_date;
         end loop;
     end loop;
end WRITE_WEEKEND_MACD_DIF;

procedure WRITE_WEEKEND_MACD_DEA as
    preEma12 number;
    preEma26 number;
    preDif number;
    preDea number;
    firstDate date;
   -- 表示stock_code
    stockWeekendCode varchar2(10);
    -- 返回全部stock_code
    cursor allStockWeekendCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 获取每只股票第一个交易周的日期
    cursor firstStockWeekendDate is select t.weekend_begin_date from stock_weekend t where t.stock_code=stockWeekendCode and rownum<=1 order by t.weekend_begin_date asc;
    -- 根据stockCode选出某只股票的除第一周以外的全部交易记录，按升序排列
    cursor allStockWeekend is select distinct * from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date>firstDate order by t.weekend_begin_date asc;
begin
     -- 计算每只股票其余交易周的ema12,ema26,dif和dea字段
     for i in allStockWeekendCode loop
         stockWeekendCode:=i.stock_code;
         -- 用记录是第一个交易周的字段初始化相关变量
         for x in firstStockWeekendDate loop
             select t.ema12,t.ema26,t.dif,t.dea into preEma12,preEma26,preDif,preDea from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date=x.weekend_begin_date;
         end loop;
         select t.weekend_begin_date into firstDate from stock_weekend t where t.stock_code=stockWeekendCode and rownum<=1 order by t.weekend_begin_date asc;
         for j in allStockWeekend loop
             update stock_weekend t set t.dea=preDea*8/10+t.dif*2/10 where t.stock_code=stockWeekendCode and t.weekend_begin_date=j.weekend_begin_date;
             commit;
             select t.dea into preDea from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date=j.weekend_begin_date;
         end loop;
     end loop;
end WRITE_WEEKEND_MACD_DEA;




/*-------------------------------- write weekend MACD of all stocks by date -----------------------------------------------*/
procedure WRITE_WEEKEND_MACD_EMA_BY_DATE(stockWeekendBeginDate in varchar2,stockWeekendEndDate in varchar2) as
    preEma12 number;
    preEma26 number;
    preDea number;
    -- 表示stock_code
    stockWeekendCode varchar2(10);
    -- 返回全部stock_code
    cursor allStockWeekendCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 获取某只股票最近两天的记录
    cursor singleStockWeekend is select * from
                                 (
                                        select * from
                                        (
                                               select * from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date<=to_date(stockWeekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date<=to_date(stockWeekendEndDate,'yyyy-mm-dd') order by t.weekend_begin_date desc
                                         ) where rownum<=2
                                 ) o order by o.weekend_begin_date asc;
begin
     for i in allStockWeekendCode loop
         stockWeekendCode:=i.stock_code;
         for j in singleStockWeekend loop
             if j.weekend_begin_date!=to_date(stockWeekendBeginDate,'yyyy-mm-dd') and j.weekend_end_date!=to_date(stockWeekendEndDate,'yyyy-mm-dd') then
                preEma12:=j.ema12;
                preEma26:=j.ema26;
                preDea:=j.dea;
             else
                 update stock_weekend t set t.ema12=preEma12*11/13+j.weekend_close*2/13,t.ema26=preEma26*25/27+j.weekend_close*2/27 where t.stock_code=stockWeekendCode and t.weekend_begin_date=to_date(stockWeekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date=to_date(stockWeekendEndDate,'yyyy-mm-dd');
                 commit;
             end if;
         end loop;
     end loop;
end WRITE_WEEKEND_MACD_EMA_BY_DATE;

procedure WRITE_WEEKEND_MACD_DIF_BY_DATE(stockWeekendBeginDate in varchar2,stockWeekendEndDate in varchar2) as
    preEma12 number;
    preEma26 number;
    preDea number;
    -- 表示stock_code
    stockWeekendCode varchar2(10);
    -- 返回全部stock_code
    cursor allStockWeekendCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 获取某只股票最近两天的记录
    cursor singleStockWeekend is select * from
                                 (
                                        select * from
                                        (
                                               select * from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date<=to_date(stockWeekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date<=to_date(stockWeekendEndDate,'yyyy-mm-dd') order by t.weekend_begin_date desc
                                         ) where rownum<=2
                                 ) o order by o.weekend_begin_date asc;
begin
     for i in allStockWeekendCode loop
         stockWeekendCode:=i.stock_code;
         for j in singleStockWeekend loop
             if j.weekend_begin_date!=to_date(stockWeekendBeginDate,'yyyy-mm-dd') and j.weekend_end_date!=to_date(stockWeekendEndDate,'yyyy-mm-dd') then
                preEma12:=j.ema12;
                preEma26:=j.ema26;
                preDea:=j.dea;
             else
                 update stock_weekend t set t.dif=t.ema12-t.ema26 where t.stock_code=stockWeekendCode and t.weekend_begin_date=to_date(stockWeekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date=to_date(stockWeekendEndDate,'yyyy-mm-dd');
                 commit;
             end if;
         end loop;
     end loop;
end WRITE_WEEKEND_MACD_DIF_BY_DATE;

procedure WRITE_WEEKEND_MACD_DEA_BY_DATE(stockWeekendBeginDate in varchar2,stockWeekendEndDate in varchar2) as
    preEma12 number;
    preEma26 number;
    preDea number;
    -- 表示stock_code
    stockWeekendCode varchar2(10);
    -- 返回全部stock_code
    cursor allStockWeekendCode is select distinct t.stock_code from stock_weekend t order by t.stock_code asc;
    -- 获取某只股票最近两天的记录
    cursor singleStockWeekend is select * from
                                 (
                                        select * from
                                        (
                                               select * from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date<=to_date(stockWeekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date<=to_date(stockWeekendEndDate,'yyyy-mm-dd') order by t.weekend_begin_date desc
                                         ) where rownum<=2
                                 ) o order by o.weekend_begin_date asc;
begin
     for i in allStockWeekendCode loop
         stockWeekendCode:=i.stock_code;
         for j in singleStockWeekend loop
             if j.weekend_begin_date!=to_date(stockWeekendBeginDate,'yyyy-mm-dd') and j.weekend_end_date!=to_date(stockWeekendEndDate,'yyyy-mm-dd') then
                preEma12:=j.ema12;
                preEma26:=j.ema26;
                preDea:=j.dea;
             else
                 update stock_weekend t set t.dea=preDea*8/10+t.dif*2/10 where t.stock_code=stockWeekendCode and t.weekend_begin_date=to_date(stockWeekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date=to_date(stockWeekendEndDate,'yyyy-mm-dd');
                 commit;
             end if;
         end loop;
     end loop;
end WRITE_WEEKEND_MACD_DEA_BY_DATE;



/*------------------------------ judge if MACD of the stock weekend is the end deviation by date -----------------------------------------*/
procedure FIND_WEEKEND_MACD_END_DEVIATE(stockWeekendBeginDate in varchar2,stockWeekendEndDate in varchar2,rateWeekendDate in varchar2,stockWeekendResult out clob) as
    -- 定义一个stock_weekend%rowtype类型的变量
    stockWeekendRecord stock_weekend%rowtype;
    -- 定义一个含有2个数值型数据的数组
    type type_array_dif_dea is varray(2) of stock_weekend%rowtype;
    arrayDifDea type_array_dif_dea:=type_array_dif_dea();
    -- 定义一个含有2560个数值型数据的数组
    type type_array_gold_cross is varray(2560) of stock_weekend%rowtype;
    arrayGoldCross type_array_gold_cross:=type_array_gold_cross();
    -- 表示stock_code
    stockWeekendCode varchar2(10);
    -- 用于遍历某只股票的所有周
    moveStockWeekendBeginDate date;
    moveStockWeekendEndDate date;
    -- 返回全部stock_code
    cursor allStockWeekendCode is select distinct t.stock_code from stock_weekend t /*where t.stock_code='sz002152'*/ order by t.stock_code asc;
    -- 获取某只股票某一周及之前所有的记录
    cursor singleStockWeekend is select * from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date<=to_date(stockWeekendBeginDate,'yyyy-mm-dd') and t.weekend_end_date<=to_date(stockWeekendEndDate,'yyyy-mm-dd') order by t.weekend_begin_date desc;
    -- 获取某只股票某一周及之前一周的记录
    cursor lastTwoDayStockWeekendInfo is select * from
                                         (
                                                select * from stock_weekend t where t.stock_code=stockWeekendCode and t.weekend_begin_date<=moveStockWeekendBeginDate and t.weekend_end_date<=moveStockWeekendEndDate order by t.weekend_begin_date desc
                                         ) where rownum<=2;
    -- 创建UTL_FILE.file_type对象，用于读写文件
    file_handle UTL_FILE.file_type;
begin
    file_handle := UTL_FILE.FOPEN('TXTDIR','SELECT_WEEKEND_MACD_END_DEVIATION.txt','w');
    stockWeekendResult:='';
     for i in allStockWeekendCode loop
         stockWeekendCode:=i.stock_code;
         arrayGoldCross:=type_array_gold_cross();
         for x in singleStockWeekend loop
             moveStockWeekendBeginDate:=x.weekend_begin_date;
             moveStockWeekendEndDate:=x.weekend_end_date;
             arrayDifDea:=type_array_dif_dea();
             for j in lastTwoDayStockWeekendInfo loop
                 stockWeekendRecord.dif:=j.dif;
                 stockWeekendRecord.dea:=j.dea;
                 stockWeekendRecord.stock_code:=j.stock_code;
                 stockWeekendRecord.Weekend_Begin_Date:=j.weekend_begin_date;
                 stockWeekendRecord.Weekend_End_Date:=j.weekend_end_date;
                 stockWeekendRecord.Weekend_Close:=j.weekend_close;
                 arrayDifDea.extend;
                 arrayDifDea(arrayDifDea.count):=stockWeekendRecord;
             end loop;
             -- 判断是否是金叉。如果是，则存入数组arrayGoldCross中
             if arrayDifDea(1).dif>arrayDifDea(1).dea and arrayDifDea(2).dif<arrayDifDea(2).dea then
                arrayGoldCross.extend;
                arrayGoldCross(arrayGoldCross.count):=arrayDifDea(1);
                if arrayGoldCross.count>1 then
                   -- 判断是否是底背离
                   if arrayGoldCross(1).weekend_close<arrayGoldCross(2).weekend_close and (arrayGoldCross(1).dif+arrayGoldCross(1).dea)/2>(arrayGoldCross(2).dif+arrayGoldCross(2).dea)/2 then
                      -- 要求在阈值日期之后。阈值一般为一周的第一个交易日
                      if arrayGoldCross(1).Weekend_Begin_Date>=to_date(rateWeekendDate,'yyyy-mm-dd') and arrayGoldCross(1).Weekend_End_Date>=to_date(rateWeekendDate,'yyyy-mm-dd') then
                          -- 设置返回值。注意：最后一个逗号是多余的
                          stockWeekendResult:=stockWeekendResult||arrayGoldCross(1).stock_code||'&'||to_char(arrayGoldCross(1).Weekend_Begin_Date,'yyyymmdd')||'&'||to_char(arrayGoldCross(1).Weekend_End_Date,'yyyymmdd')||',';
                          dbms_output.put(arrayGoldCross(1).stock_code);
                          dbms_output.put_line('    '||arrayGoldCross(1).Weekend_Begin_Date);
                          dbms_output.put_line('    '||arrayGoldCross(1).Weekend_End_Date);
                          UTL_FILE.PUT_LINE(file_handle,arrayGoldCross(1).stock_code||'    '||arrayGoldCross(1).Weekend_Begin_Date||'    '||arrayGoldCross(1).Weekend_End_Date);
                      end if;
                   end if;
                   goto next;
                end if;
             end if;
         end loop;
         <<next>>
         null;
     end loop;
end FIND_WEEKEND_MACD_END_DEVIATE;



----------------------------------------- select the gold cross of kd index in weekend scale -----------------------------------------
procedure SELECT_WEEKEND_KD_UP(beginDate in varchar2,endDate in varchar2,crossPoint in number,stockWeekendResultArray out T_STOCK_WEEKEND_RESULT_ARRAY) as
     -- 表示某只股票的STOCK_CODE字段
     stockCode varchar2(10);
     -- 定义一个含有6360个stock_weekend记录的数组
     type type_array is varray(6360) of stock_weekend%rowtype;
     stockWeekendRecord type_array:=type_array();
     -- 定义T_STOCK_WEEKEND_RESULT对象，用于返回记录查询到的STOCK_CODE
     stockWeekendResult T_STOCK_WEEKEND_RESULT;
     -- 表示某一周的所有股票代码
     cursor selectedStockWeekendCode is select t.stock_code from stock_weekend t where t.weekend_begin_date=to_date(beginDate,'yyyy-mm-dd') and t.weekend_end_date=to_date(endDate,'yyyy-mm-dd');
     --cursor 表示某只股票某一周及其之后一周的记录
     cursor lastTwoStockWeekend is select * from (select * from stock_weekend t where t.stock_code=stockCode and
                t.weekend_begin_date<=to_date(beginDate,'yyyy-mm-dd') and t.weekend_end_date<=to_date(endDate,'yyyy-mm-dd')
                order by t.weekend_end_date desc
            ) where rownum<=2;
     -- 创建UTL_FILE.file_type对象，用于读写文件
     file_handle UTL_FILE.file_type;
begin
     file_handle := UTL_FILE.FOPEN('TXTDIR','SELECT_WEEKEND_KD_UP.txt','w');
     stockWeekendResultArray:=T_STOCK_WEEKEND_RESULT_ARRAY();

     for i in selectedStockWeekendCode loop
          stockCode:=i.stock_code;
          for j in lastTwoStockWeekend loop
              stockWeekendRecord.extend;
              stockWeekendRecord(stockWeekendRecord.count).k:=j.k;
              stockWeekendRecord(stockWeekendRecord.count).d:=j.d;
          end loop;
          if stockWeekendRecord(1).k>stockWeekendRecord(1).d then
             if stockWeekendRecord(2).k<stockWeekendRecord(2).d then
                if (stockWeekendRecord(1).k+stockWeekendRecord(1).d+stockWeekendRecord(2).k+stockWeekendRecord(2).d)/4<=crossPoint then
                   stockWeekendResultArray.extend;
                   stockWeekendResult:=T_STOCK_WEEKEND_RESULT(i.stock_code,to_date(beginDate,'yyyy-mm-dd'),to_date(endDate,'yyyy-mm-dd'));
                   stockWeekendResultArray(stockWeekendResultArray.count):=stockWeekendResult;
                   UTL_FILE.PUT_LINE(file_handle,i.stock_code);
                end if;
             end if;
          end if;
          stockWeekendRecord:=type_array();
     end loop;
end SELECT_WEEKEND_KD_UP;



end PKG_WEEKEND;